package com.mdjukebox.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.mdjukebox.model.vo.CancionVO;
import com.mdjukebox.util.GlobalNames;
import com.util.configuration.ConfigurationParametersManager;
import com.util.configuration.MissingConfigurationParameterException;
import com.util.exceptions.InstanceNotFoundException;
import com.util.exceptions.InternalErrorException;
import com.util.sql.GeneralOperations;

public class CancionDAO {
	private static Log log = LogFactory.getLog(CancionDAO.class);

	
	/*
	 * Para obtener la proxima cancion
	 * select *
		from cancion
		where not fchprmpet is null and (fchultrep is null or (now() - fchultrep) > '01:00:00' )
		order by fchprmpet, fchultrep 
	 */ 
	public CancionVO obtenerProximaCancion(Connection connection)
	throws InternalErrorException, SQLException {
		
	    PreparedStatement preparedStatement = null;
	    ResultSet resultSet = null;
		CancionVO cancion = null;

	    try {
        	String txtMinEntreCanciones = "01:00:00";
			try{
				txtMinEntreCanciones = ConfigurationParametersManager.getParameter(GlobalNames.PARAM_MIN_ENTRE_CANCIONES_LONG_FORMAT);
			}catch(MissingConfigurationParameterException e){
				if (log.isWarnEnabled()) log.warn("No se ha encontrado el parametro " + GlobalNames.PARAM_MIN_ENTRE_CANCIONES_LONG_FORMAT);
			}
			
			/* Create "preparedStatement". */
			//canciones con peticiones pendientes (fchprmpet not null)
			//y que no se han reproducido la ultima hora 
			String queryString = "SELECT * FROM cancion " +
			"WHERE NOT fchprmpet is null AND (fchultrep is null or (now() - fchultrep) > ? ) " +
			"ORDER BY fchprmpet, fchultrep";
	
			preparedStatement = connection.prepareStatement(queryString);    
			preparedStatement.setString(1, txtMinEntreCanciones);
	
			/* Execute query. */
			ResultSet rs = preparedStatement.executeQuery();
	
			/* Ha devuelto resultados, nos quedamos con la primera cancion */
			if ( rs.next() ) {
				cancion = new CancionVO (rs.getInt("id"), 
						rs.getString("titulo"), 
						rs.getString("autor"), 
						rs.getTime("duracion"),
						rs.getString("caratula"), 
						rs.getBoolean("dedicable"), 
						rs.getTimestamp("fchultrep"), 
						rs.getTimestamp("fchprmpet"), 
						rs.getString("path") );				
			}
	
			return cancion; 
	    } catch (SQLException e) {
	        throw new InternalErrorException(e);    
	    } finally {
	        GeneralOperations.closeResultSet(resultSet);
	        GeneralOperations.closeStatement(preparedStatement);
	    }
	}
	
	
	/*
	 * Lista a descartar: canciones se han reproducido la ultima hora
	 * select *
		from cancion
		where (now() - fchultrep) < '01:00:00' 
		order by fchprmpet, fchultrep 
	 */
	public ArrayList<CancionVO> getDescartes(Connection connection)
	throws InternalErrorException {
	    PreparedStatement preparedStatement = null;
	    ResultSet resultSet = null;
	    ArrayList<CancionVO> lista = new ArrayList<CancionVO>();

	    try {
	    	
        	String txtMinEntreCanciones = "01:00:00";
			try{
				txtMinEntreCanciones = ConfigurationParametersManager.getParameter(GlobalNames.PARAM_MIN_ENTRE_CANCIONES_LONG_FORMAT);
			}catch(MissingConfigurationParameterException e){
				if (log.isWarnEnabled()) log.warn("No se ha encontrado el parametro " + GlobalNames.PARAM_MIN_ENTRE_CANCIONES_LONG_FORMAT);
			}
			
			String queryString = "SELECT id, titulo, autor, duracion, caratula, dedicable, fchultrep, fchprmpet, path " +
			"FROM cancion " +
			"WHERE (now() - fchultrep) < ? " +
			"ORDER BY fchprmpet, fchultrep";
			
			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setString(1, txtMinEntreCanciones);
			
			/* Execute query. */
			ResultSet rs = preparedStatement.executeQuery();
			
			while ( rs.next() ) {
				CancionVO cancion = new CancionVO (rs.getInt("id"), 
						rs.getString("titulo"), 
						rs.getString("autor"), 
						rs.getTime("duracion"),
						rs.getString("caratula"), 
						rs.getBoolean("dedicable"), 
						rs.getTimestamp("fchultrep"), 
						rs.getTimestamp("fchprmpet"), 
						rs.getString("path") );	
				lista.add(cancion);
			}
			return lista;
	    } catch (SQLException e) {
	        throw new InternalErrorException(e);    
	    } finally {
	        GeneralOperations.closeResultSet(resultSet);
	        GeneralOperations.closeStatement(preparedStatement);
	    }
	}
			
	
	public int getTotalCanciones(Connection connection)
	throws InternalErrorException {
        
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {

        /* Create "preparedStatement". */
        String queryString = "SELECT count(*)" +
        	" FROM Cancion";
        preparedStatement = connection.prepareStatement(queryString);
                
        /* Execute query. */
        resultSet = preparedStatement.executeQuery();
        
        /* Get results. */
        int rdo = 0;
        if (resultSet.next()){
        	rdo = resultSet.getInt(1);
        }
                
        /* Return the value object. */
        return rdo;
        
    } catch (SQLException e) {
        throw new InternalErrorException(e);    
    } finally {
        GeneralOperations.closeResultSet(resultSet);
        GeneralOperations.closeStatement(preparedStatement);
    }    
    
}
	
	public CancionVO getCancionById(Connection connection, int idCancion)
		throws InstanceNotFoundException, InternalErrorException {
	        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            /* Create "preparedStatement". */
            String queryString = "SELECT titulo, autor, duracion, caratula, dedicable, fchUltRep, fchPrmPet, path" +
            	" FROM Cancion WHERE" +
                " id = ?";
            preparedStatement = connection.prepareStatement(queryString);
            
            /* Fill "preparedStatement". */
            int i = 1;
            preparedStatement.setInt(i++, idCancion);
            
            /* Execute query. */
            resultSet = preparedStatement.executeQuery();
            
            if (!resultSet.next()) {
                throw new InstanceNotFoundException(idCancion, CancionVO.class.getName());
            }

            /* Get results. */
            i = 1;
            String titulo = resultSet.getString(i++);
            String autor = resultSet.getString(i++);
            Time duracion = resultSet.getTime(i++);
            String caratula = resultSet.getString(i++);
            boolean dedicable = resultSet.getBoolean(i++);
            Timestamp fchUltRep = resultSet.getTimestamp(i++);
            Timestamp fchPrmPet = resultSet.getTimestamp(i++);
            String path = resultSet.getString(i++);
            
            CancionVO vo =
                new CancionVO(idCancion, titulo, autor, duracion, caratula, dedicable, fchUltRep, fchPrmPet, path);
            
            /* Return the value object. */
            return vo;
            
        } catch (SQLException e) {
            throw new InternalErrorException(e);    
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }    
        
    }
	
    public void updateCancion(Connection connection, CancionVO vo) 
	    throws InstanceNotFoundException, InternalErrorException {
	    
	    PreparedStatement preparedStatement = null;
	
	    try {
	
	        /* Create "preparedStatement". */
	        String queryString = "UPDATE cancion" +
	            " SET fchPrmPet = ?, fchUltRep = ? WHERE id = ?";
	        preparedStatement = connection.prepareStatement(queryString);
	        
	        /* Fill "preparedStatement". */
	        int i = 1;
	        preparedStatement.setTimestamp(i++,vo.getFchPrmPet());
	        preparedStatement.setTimestamp(i++,vo.getFchUltRep());
	        preparedStatement.setInt(i++,vo.getId());
	        
	        /* Execute query. */
	        int updatedRows = preparedStatement.executeUpdate();
	
	        if (updatedRows == 0) {
	            throw new InstanceNotFoundException(
	                vo.getId(), 
	                CancionVO.class.getName());
	        }else{
	        	log.debug("  --- Cancion actualizada");
	        }
		        
	    } catch (SQLException e) {
	        throw new InternalErrorException(e);    
	    } finally {
	        GeneralOperations.closeStatement(preparedStatement);
	    }            
	    
	}
    
    public ArrayList<CancionVO> getCancionesByDedicable(Connection connection)
	throws InternalErrorException {
		
		ArrayList<CancionVO> lista = new ArrayList<CancionVO>();
		
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try{
		
			String queryString = "SELECT * FROM cancion " +
									"WHERE dedicable ORDER BY id";
	
			preparedStatement = connection.prepareStatement(queryString);    
	
			/* Execute query. */
			rs = preparedStatement.executeQuery();

			while ( rs.next() ) {
				CancionVO cancion = new CancionVO (rs.getInt("id"), 
						rs.getString("titulo"), 
						rs.getString("autor"), 
						rs.getTime("duracion"),
						rs.getString("caratula"), 
						rs.getBoolean("dedicable"), 
						rs.getTimestamp("fchultrep"), 
						rs.getTimestamp("fchprmpet"), 
						rs.getString("path") );	
				lista.add(cancion);
			}
			
			GeneralOperations.closeStatement(preparedStatement);
			
			return lista;
	    } catch (SQLException e) {
	        throw new InternalErrorException(e);    
	    } finally {
	        GeneralOperations.closeResultSet(rs);
	        GeneralOperations.closeStatement(preparedStatement);
	    }

	}
    
    public ArrayList<CancionVO> getCanciones (Connection connection)
	throws InternalErrorException, SQLException {
		
		ArrayList<CancionVO> lista = new ArrayList<CancionVO>();
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try{
		
			String queryString = "SELECT * FROM cancion ORDER BY id";
	
			preparedStatement = connection.prepareStatement(queryString);    
	
			/* Execute query. */
			rs = preparedStatement.executeQuery();
			
			while ( rs.next() ) {
				CancionVO cancion = new CancionVO (rs.getInt("id"), 
						rs.getString("titulo"), 
						rs.getString("autor"), 
						rs.getTime("duracion"),
						rs.getString("caratula"), 
						rs.getBoolean("dedicable"), 
						rs.getTimestamp("fchultrep"), 
						rs.getTimestamp("fchprmpet"), 
						rs.getString("path") );	
				lista.add(cancion);
			}
			
			GeneralOperations.closeStatement(preparedStatement);
			
			return lista;
	    } catch (SQLException e) {
	        throw new InternalErrorException(e);    
	    } finally {
	        GeneralOperations.closeResultSet(rs);
	        GeneralOperations.closeStatement(preparedStatement);
	    }
	}

}
